////////////////////////////////
//mysql.lua
//Defines mysql functions.
//DEFINES
////////////////////////////////

//This uses andyvincent's mysql module, and little bits of his code. Thanks.
function bbb.connect()
	local db, error = mysql.connect("127.0.0.1","bbb","vpswildcats7","bbb")
	local dbt=db
	while(dbt>1) do //This loops through active connections and kills them.
		dbt=dbt-1
		mysql.disconnect(dbt)
	end
	if(db==0) then
		bbb.error("MYSQL CONNECT","MySQL error: "..tostring(error))
		return false
	end
	bbb.db=db
	return true
end
function bbb.disconnect()
	local isok, error = mysql.disconnect(bbb.db)
	if(isok!=true) then //Not ok.
		bbb.error("MYSQL DISCONNECT","MySQL error: "..tostring(error))
		return false
	end
	bbb.db=nil
	return true
end
function bbb.query(sqlstring)
	if(type(sqlstring)!="string") then
		bbb.error("MYSQL QUERY","Bad input.")
		return false
	end
	if(bbb.db==nil) then
		bbb.error("MYSQL QUERY","No database.")
		return false
	end
	result, isok, error = mysql.query(bbb.db,sqlstring);
	if(isok!=true) then //Not ok.
		bbb.error("MYSQL QUERY","MySQL error: "..tostring(error).."\nIn query:\n"..sqlstring)
		return false
	end
	return result
end
//This function uses a thread to handle the function. When it completes it will call handlefunc with the results of the query.
//Handlefunc is optional.
function bbb.threadedquery(sqlstring,handlefunc)
	if(type(sqlstring)!="string") then
		bbb.error("MYSQL THREADED QUERY","Bad input.")
		return false
	end
	if(bbb.db==nil) then
		bbb.error("MYSQL THREADED QUERY","No database.")
		return false
	end
	local thread, isok, error = mysql.query(bbb.db,sqlstring,mysql.QUERY_NOBLOCK)
	if(thread) then //If the thread started then start checking it.
		timer.Simple(.5,bbb.checkthread,thread,handlefunc)
	else
		bbb.error("MYSQL THREADED QUERY","MySQL (thread) error: "..tostring(error))
	end
	return true
end
function bbb.checkthread(threadid,handlefunc)
	if(mysql.query_complete(threadid)) then //If it finished...
		result,isok,error=mysql.query_read(threadid) //Nab the result
		if(isok!=true) then
			bbb.error("MYSQL THREADED QUERY","MySQL error: "..tostring(error)) //Error
		else //Worked
			if(type(handlefunc)=="function") then //If there was a function, run it.
				handlefunc(result)
			end
		end
		mysql.query_cleanup(threadid) //Clean up?
	else
		timer.Simple(1,bbb.checkthread,threadid,handlefunc) //If it didn't finished continue checking.
	end
end
function bbb.escape(input)
	if(input==nil) then
		bbb.error("ESCAPE","INSTA CRASH WARNING! ATTEMPTING TO ESCAPE nil!")
		return ""
	end
	return mysql.escape(bbb.db,tostring(input))
end

//Upload will upload a table.
function bbb.upload(totable,fromtable,key,threaded)
	if(type(totable)!="string") then
		bbb.error("UPLOAD","Bad upload table.")
		return false
	end
	if(type(fromtable)!="table") then
		bbb.error("UPLOAD","Upload table is bad, will not work.")
		return false
	end
	
	//The following series will build the where part of the sql statment. This determines what to change or whatever.
	if(type(key)=="string") then
		key={key} //Throw it in a table to be handled by the next section.
	end
	local whereclause="WHERE 1" //By default anything goes.
	if(type(key)=="table") then
		whereclause=""
		for _,col in ipairs(key) do
			if(fromtable[col]==nil) then
				bbb.error("UPLOAD","Key: '"..col.."' does not exist in table.")
				return false
			end
			whereclause=whereclause..col.."='"..bbb.escape(fromtable[col]).."' AND "
		end
		if(whereclause!="") then
			whereclause="WHERE "..string.sub(whereclause,1,-6) //The sub strips off the last and
		else
			bbb.error("UPLOAD","Empty or malformed table given as key.")
			return false
		end
	end

	totable=bbb.escape(totable) //Keep bad stuff out.
	key=bbb.escape(key)
	local query
	local query2=""
	
	if(#bbb.query("SELECT 0 FROM `"..totable.."` "..whereclause)==0) then //If the table is empty
		query="INSERT INTO `"..totable.."` (" //Insert a new row
		query2=") VALUES ("
	else
		query="UPDATE `"..totable.."` SET" //Update an existing row
	end
	
	for col,value in pairs(fromtable) do
		if(query2=="") then //If it isn't the values syntax..
			if(col!=key) then //We already know that the key is updated, since it exists.
				query=query.." "..col.."='"..bbb.escape(value).."',"  //column="data", 
			end
		else
			query=query..col.."," //Values syntax
			query2=query2.."'"..bbb.escape(value).."',"
		end
	end
	
	if(query!="INSERT INTO `"..totable.."` (" and query!="UPDATE `"..key.."` SET") then //If it changed...
		if(query2=="") then
			query=string.sub(query,1,-2).." "..whereclause //Remove the last comman and add the where
		else
			query=string.sub(query,1,-2)..string.sub(query2,1,-2)..");"
		end
		if(threaded) then
			bbb.threadedquery(query)
		else
			bbb.query(query)
		end
	end
end

function bbb.verifytables()
	local currenttables={}
	for k,v in ipairs(bbb.query("SHOW TABLES") or {}) do
		currenttables[v[1]]=true //Reformat the table into currenttables['table']=true
	end
	local query="" //The global be all end all query to fix the tables up.
	local contents //If something needs to be filled over time then this will hold the contents. Ex: CREATE TABLE (contents)
	local currentlayout, pos, pos2, curkey
	local finishquery="" //Anything really dangerous (Read: drop) goes in here to be manually executed.
	for k,v in pairs(bbb.databaselayout) do //With everything there...
		if(currenttables[k]!=true) then //Create the table, fairly straightforward.
			contents=""
			for tab,info in pairs(v) do
				contents=contents.." "..bbb.mysqlcreatecolumn(tab,info)..",\n" //Build up the columns
			end
			if(contents!="") then //If there were any...
				//Create the table. The sub will remove that pesky trailing comma.
				query=query.."CREATE TABLE `"..k.."` ("..string.sub(contents,1,-3)..");\n"
			end
		else //It exists, make sure it is correct.
			currenttables[k]=nil //Anything not hit by this needs to go.
			currentlayout={}
			//Iterate through the current setup
			for col,info in ipairs(bbb.query("DESCRIBE `"..k.."`") or {}) do
				//These two will encase the length allowing it to be detected. Ex: TYPE (length) UNSIGNED
				currentlayout[info[1]]={
					null=info[3],
					key=info[4],
					default=info[5]
				}
				pos=string.find(info[2],"(",1,true)
				if(pos!=nil) then
					pos2=string.find(info[2],")",pos,true)
					currentlayout[info[1]].type=string.upper(string.sub(info[2],1,pos-1))
					currentlayout[info[1]].length=string.sub(info[2],pos+1,pos2-1)
					currentlayout[info[1]].unsigned=string.sub(info[2],pos2+2)
				else
					pos=string.find(info[2]," ",1,true)
					if(pos!=nil) then
						currentlayout[info[1]].type=string.upper(string.sub(info[2],1,pos-1))
						currentlayout[info[1]].unsigned=string.sub(info[2],pos+1)
					else
						currentlayout[info[1]].type=string.upper(info[2])
					end
				end
				//These format it's strings into nils or no nils.
				if(currentlayout[info[1]].length=="") then
					currentlayout[info[1]].length=nil
				end
				if(currentlayout[info[1]].unsigned=="unsigned") then
					currentlayout[info[1]].unsigned=true
				else
					currentlayout[info[1]].unsigned=nil
				end
				if(currentlayout[info[1]].null=="NO") then
					currentlayout[info[1]].null=nil
				else
					currentlayout[info[1]].null=true
				end
				if(currentlayout[info[1]].key=="PRI") then
					currentlayout[info[1]].key=true
				else
					currentlayout[info[1]].key=nil
				end
			end
			contents=""
			//Iterate through the specified columns in this table
			for col,info in pairs(v) do
				if(currentlayout[col]==nil) then //It doesn't exist, add.
					contents=contents.."ADD "..bbb.mysqlcreatecolumn(col,info)..",\n"
				else
					if(info.key==false and currentlayout[col].key==true) then
						contents=contents.."DROP PRIMARY KEY,\n"
					end
					//If they differ in some respect...
					if(currentlayout[col].type!=info.type or
					(currentlayout[col].length!=tostring(info.length) and info.length!=nil) or
					currentlayout[col].unsigned!=info.unsigned or
					currentlayout[col].null!=info.null or
					currentlayout[col].key!=info.key or
					currentlayout[col].default!=info.default) then
						contents=contents.."MODIFY "..bbb.mysqlcreatecolumn(col,info)..",\n"
					end
				end
				currentlayout[col]=nil //Anything leftover will be deleted
			end
			//If the query was added to...
			if(contents!="") then
				//The sub removes the comma and the newline
				query=query.."ALTER TABLE `"..k.."` "..string.sub(contents,1,-3)..";\n"
			end
			for col in pairs(currentlayout) do //The leftovers
				finishquery=finishquery.."ALTER TABLE `"..k.."` DROP '"..col.."';\n"
			end
		end
	end
	for k,v in pairs(currenttables) do //Add the left over tables to the drop list
		finishquery=finishquery.."DROP TABLE `"..k.."`;\n"
	end
	if(query!="") then
		//Query only allows one query at a time. Split by ";\n", which was used to seperate everything.
		for key,splitquery in ipairs(string.Explode(";\n",query)) do
			if(splitquery!="\n") then //The splitting leaves one of these.
				bbb.query(splitquery)
			end
		end
	end
	if(finishquery!="") then
		bbb.print("To finish table verification, run this:",bbb.CONSOLE+bbb.LOG)
		bbb.print(finishquery,bbb.CONSOLE+bbb.LOG)
	end
end

function bbb.mysqlcreatecolumn(name,specs)
	local output=name.." "..specs.type
	if(specs.length) then
		output=output.." ("..tostring(specs.length)..")"
	end
	if(specs.unsigned) then
		output=output.." UNSIGNED"
	end
	if(specs.null==nil) then
		output=output.." NOT NULL"
	end
	if(specs.default and specs.default!="") then
		output=output.." DEFAULT '"..bbb.escape(specs.default).."'"
	end
	if(specs.key) then
		output=output.." PRIMARY KEY"
	end
	
	return output
end